|
加入vbs脚本编程,同时开启宏。但是如果用户电脑不开启宏,意味着生成的excel中vbs脚本失效。操作步骤如下：  点击开发工具,启动宏,如果wps的话开启需要安装插件  点击根据步骤1,2,3将vbs代码复制黏贴进去，vbs代码在文章底部。 重点!重点!重点!最后记得要加密整个工作簿，可以编辑的单元格要设置取消锁定（选中单元格，鼠标右键后点击单元格格式）。   vbs代码如下:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As String
For Each rng In Target
If Not rng.Validation.Value Then
Application.Undo
Dim msg As String
msg = "粘贴的数据不符合校验规则：位置在第" & rng.Row & "行，第" & getColumnName(rng.column) & "列，请仔细检查"
MsgBox prompt:=msg, Title:="输入提示"
Exit For
End If
Next
End Sub
Private Function getColumnName(column As Integer) As String
Dim alphabet(26) As String
alphabet(0) = "A"
alphabet(1) = "B"
alphabet(2) = "C"
alphabet(3) = "D"
alphabet(4) = "E"
alphabet(5) = "F"
alphabet(6) = "G"
alphabet(7) = "H"
alphabet(8) = "I"
alphabet(9) = "J"
alphabet(10) = "K"
alphabet(11) = "L"
alphabet(12) = "M"
alphabet(13) = "N"
alphabet(14) = "O"
alphabet(15) = "P"
alphabet(16) = "Q"
alphabet(17) = "R"
alphabet(18) = "S"
alphabet(19) = "T"
alphabet(20) = "U"
alphabet(21) = "V"
alphabet(22) = "W"
alphabet(23) = "X"
alphabet(24) = "Y"
alphabet(25) = "Z"
If (column < 27) Then
getColumnName = alphabet(column - 1)
Else
Dim i, j As Integer
i = column \ 26
j = column Mod 26
If (i < 26) Then
getColumnName = alphabet(i - 1) & alphabet(j - 1)
Else
getColumnName = column
End If
End If
End Function
|